﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Mono.TextTemplating;
using Newtonsoft.Json;
using WHLRDF.Application.BLL;
using WHLRDF.Application.Model;
using WHLRDF.Code.Model;
using WHLRDF.ORM;

namespace WHLRDF.Code.BLL
{
    /// <summary>
    /// SQL 相关操作类
    /// </summary>
    public partial class DbCodeService
    {
        /// <summary>
        /// 更新字段描述
        /// </summary>
        /// <param name="dbColumn"></param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public bool UpdateColumnDescription(DbColumnEntity dbColumn, ref string strError)
        {
            if (dbColumn.DataCollection == null || dbColumn.DataCollection.Count <= 0 || !dbColumn.DataCollection.ContainsKey("ColumnCaption"))
            {
                return true;
            }
            var targetTable = this.GetById<DbTableEntity>(dbColumn.DbTableId.ToString());
            if (targetTable == null)
            {
                strError = "表名不存在";
                return false;
            }
            var targetServer = this.GetById<DbServerEntity>(targetTable.DbServerId.ToString());
            if (targetServer == null)
            {
                strError = "数据库源服务器不存在或已删除！";
                return false;
            }

            using (var targetHelper = this.CreateRepository(targetServer.ProviderType, targetServer.ConntionString))
            {
                string existSql = targetHelper.ExistDescriptionSql(targetTable.DbTableName, dbColumn.ColumnName);
                var exist = targetHelper.Scalar(existSql);
                string updateSql = targetHelper.UpdateDescriptionSql(targetTable.DbTableName, dbColumn.ColumnName, dbColumn.ColumnCaption, (exist == null || exist.ToString() != "1"));
                using (var tran = targetHelper.Begin())
                {
                    try
                    {
                        targetHelper.Execute(updateSql);
                        targetHelper.Commit();
                    }
                    catch (Exception ex)
                    {
                        strError = ex.Message;
                        targetHelper.Rollback();
                        return false;
                    }
                }
            }
            return true;
        }
        #region 构建sql

        /// <summary>
        /// 创建数据库实例
        /// </summary>
        /// <param name="providerType"></param>
        /// <param name="conntionString"></param>
        /// <returns></returns>
        private IDbRepository CreateRepository(int providerType, string conntionString)
        {
            var provider = (ProviderType)providerType;
            var dbProviderType = ApplicationEnvironments.Site.GetProviderType(provider.ToString());
            var providerVersion = ApplicationEnvironments.Site.GetProviderVersion(provider.ToString());
            return this.CreateRepository(dbProviderType, conntionString, providerVersion);
        }

        private string GetInsert(IDbRepository dbRepository, string tableName, Dictionary<string, object> entity, ref DataParameterCollection parameter)
        {
            if (parameter == null)
                parameter = new DataParameterCollection(dbRepository);
            StringBuilder strsql = new StringBuilder();
            StringBuilder strParam = new StringBuilder();
            strsql.Append(" insert into " + tableName);
            strsql.Append("(");
            strParam.Append(" values(");
            int index = 0;
            foreach (var item in entity)
            {
                if (index > 0)
                {
                    strsql.Append(",");
                    strParam.Append(",");
                }
                string parameterName = dbRepository.GetParameter(item.Key);
                strsql.Append(item.Key);
                strParam.Append(parameterName);
                index++;

                parameter.Add(new DataParameter(item.Key, (item.Value == null) ? DBNull.Value : item.Value));
            }
            strsql.Append(")");

            strParam.Append(")");
            strsql.Append(strParam.ToString());

            return strsql.ToString();
        }

        /// <summary>
        /// 获取更新Sql
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="primaryKey"></param>
        /// <param name="entity"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        private string GetUpdateSql(IDbRepository dbRepository, string tableName, string primaryKey, Dictionary<string, object> entity, ref DataParameterCollection parameter)
        {
            if (parameter == null)
                parameter = new DataParameterCollection(dbRepository);
            StringBuilder strsql = new StringBuilder();
            strsql.Append(" update " + tableName + " set ");
            int index = 0;
            foreach (var item in entity)
            {
                if (item.Key.ToLower().Equals(primaryKey.ToLower()))
                {
                    continue;
                }
                if (index > 0)
                {
                    strsql.Append(",");
                }
                string parameterName = dbRepository.GetParameter(item.Key);
                strsql.Append($"{item.Key}={parameterName}");
                index++;
                parameter.Add(new DataParameter(item.Key, (item.Value == null) ? DBNull.Value : item.Value));
            }
            string parameterPrimaryName = dbRepository.GetParameter(primaryKey);
            strsql.Append($" where {primaryKey}={parameterPrimaryName} ");
            parameter.Add(new DataParameter(primaryKey, (entity[primaryKey] == null) ? DBNull.Value : entity[primaryKey]));
            return strsql.ToString();
        }

        /// <summary>
        /// 获取
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="primaryKey"></param>
        /// <returns></returns>
        private string GetExistSql(IDbRepository dbRepository, string tableName, string primaryKey)
        {
            StringBuilder strsql = new StringBuilder();
            string parameterPrimaryName = dbRepository.GetParameter(primaryKey);
            strsql.Append($" select 1 from {tableName} where {primaryKey}={parameterPrimaryName}");
            return strsql.ToString();
        }
        #endregion


        /// <summary>
        /// 获取新增的sql
        /// </summary>
        /// <param name="lstColumn"></param>
        /// <param name="rowItem"></param>
        /// <param name="resultRow"></param>
        /// <param name="index"></param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public bool GetImportInsert(List<DbColumnEntity> lstColumn, Dictionary<string, string> rowItem,
             int index, ref Dictionary<string, object> resultRow,
           ref string strError)
        {
            bool flag = false;
            foreach (var column in lstColumn)
            {
                if (!column.IsEnabled)
                {
                    continue;
                }
                if (column.IsPrimary && column.IsIdentifier)
                {
                    continue;
                }
                object itemValue = null;
                var row = rowItem.Where(x => x.Key.ToLower().Equals(column.ColumnName.ToLower())
                || (!string.IsNullOrWhiteSpace(column.ColumnCaption) && x.Key.ToLower().Equals(column.ColumnCaption.ToLower()))).FirstOrDefault();

                if (!row.Equals(default(KeyValuePair<string, string>)) && !string.IsNullOrWhiteSpace(row.Value))
                {
                    flag = CheckValidHelper.CheckInput("第" + index + "行 第" + column.ColumnCaption + "(" + column.ColumnName + ")列", column.AllowDBNull,
                 column.VaildKey, true, row.Value.ToString(), 0, column.MaxLength, column.Min, column.Max, column.RegValidate, ref strError, column.DbTypeName);
                    if (!flag)
                    {
                        break;
                    }
                    itemValue = GetValue(column, column.DbTypeName.ToLower(), row.Value);
                    resultRow.Add(column.ColumnName, itemValue);
                }
                else if (SystemColumnNames.Contains(column.ColumnName.ToLower()))
                {
                    switch (column.ColumnName)
                    {
                        case EntityBase.__CreateBy:
                        case EntityBase.__LastModifyUserId:
                            resultRow.Add(column.ColumnName, ApplicationEnvironments.DefaultSession.UserId);
                            break;
                        case EntityBase.__CreateDate:
                        case EntityBase.__LastModifyDate:
                            resultRow.Add(column.ColumnName, DateTime.Now);
                            break;
                        case EntityBase.__IsDeleted:
                            resultRow.Add(column.ColumnName, false);
                            break;
                    }
                }
                else if (!column.AllowDBNull && string.IsNullOrWhiteSpace(row.Value))
                {
                    strError = "第" + (index) + "行 第" + column.ColumnCaption + "(" + column.ColumnName + ")列不能为空";
                    return false;
                }
            }

            return flag;
        }

        /// <summary>
        /// 构建修改Sql
        /// </summary>
        /// <param name="lstColumn"></param>
        /// <param name="rowItem"></param>
        /// <param name="index"></param>
        /// <param name="resultRow"></param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public bool GetImportUpdate(List<DbColumnEntity> lstColumn, Dictionary<string, string> rowItem,
           int index, ref Dictionary<string, object> resultRow,
         ref string strError)
        {
            bool flag = false;
            foreach (var cellItem in rowItem)
            {
                var column = lstColumn.Where(x => x.ColumnName.ToLower().Equals(cellItem.Key.ToLower())
                 || (!string.IsNullOrWhiteSpace(x.ColumnCaption) && x.ColumnCaption.ToLower().Equals(cellItem.Key.ToLower()))).FirstOrDefault();
                if (column == null)
                {
                    strError = $"第{index}行 第({cellItem.Key})列未找到匹配字段";
                    return false;
                }
                if (!column.IsEnabled)
                {
                    strError = $"第{index}行 第({cellItem.Key})列未启用";
                    return false;
                }
                if (!column.AllowDBNull && string.IsNullOrWhiteSpace(cellItem.Value))
                {
                    strError = "第" + (index) + "行 第" + column.ColumnCaption + "(" + column.ColumnName + ")列不能为空";
                    return false;
                }
                if (!string.IsNullOrWhiteSpace(cellItem.Value))
                {
                    flag = CheckValidHelper.CheckInput("第" + index + "行 第" + column.ColumnCaption + "(" + column.ColumnName + ")列", column.AllowDBNull,
                 column.VaildKey, true, cellItem.Value, 0, column.MaxLength, column.Min, column.Max, column.RegValidate, ref strError, column.DbTypeName);
                    if (!flag)
                    {
                        break;
                    }
                    var itemValue = GetValue(column, column.DbTypeName.ToLower(), cellItem.Value);
                    resultRow.Add(column.ColumnName, itemValue);
                }

            }

            return flag;
        }

    }
}
